/**
 * 
 */
package com.gkwave.module.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.gkwave.module.bean.GymYogaBean;
import com.gkwave.module.bean.MessageBean;

/**
 * @author Administrator
 *
 */
public class GymYogaDAO extends BaseDAO {
	private Statement stmt = null;
	private ResultSet rset = null;
	private Connection con = null;
	/**
	 * 
	 */
	public GymYogaDAO() {
		// TODO Auto-generated constructor stub
	}

	public GymYogaBean getResultList(String query) {
		// TODO Auto-generated method stub
		System.out.println("Query: " + query);
		BaseDAO dao = null;
		GymYogaBean xbean = null;
		try {
			dao = new BaseDAO();
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			GymYogaBean bean = null;
			
			if(!rset.wasNull()){
				System.out.println("rest is not null");
				xbean = new GymYogaBean();
			}
			while (rset.next()) {
				Integer id = rset.getInt("id");
				String name = rset.getString("name");
				String nmBranch = rset.getString("t2.nm_branch");
				String idEmail = rset.getString("t1.id_email");
				Integer nrTotalSeat = rset.getInt("t1.nr_total_seat");
				Integer nrFee = rset.getInt("t1.nr_fee");
				String slotTime1 = rset.getString("t1.nm_slot_time1");
				String slotTime2 = rset.getString("t1.nm_slot_time2");
				bean = new GymYogaBean(id,name,nmBranch,idEmail,nrTotalSeat,nrFee,slotTime1,slotTime2);
				xbean.entries.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return xbean;
	}

	public MessageBean doGymRegister(Integer idUser, Integer idCenter, Integer nrTotalSeatCapacity,String idSlot,
									 String idAssociate, String nmName, String nmLocation, String inGender) {
		// TODO Auto-generated method stub
		MessageBean bean = null;
		String query = "SELECT COUNT(*) AS booked_seat FROM h_gym_booking h WHERE in_active='Y' and id_slot = '"+idSlot+"' and id_gym=" + idCenter;
		Integer nrBookedSeat = getTotalBookedSeat(nrTotalSeatCapacity,query);
		System.out.println("Total / Booked - sheats: " + nrTotalSeatCapacity + " / " +nrBookedSeat);
		if(isSeatAvailable(nrTotalSeatCapacity,nrBookedSeat)){
			String query1 = "INSERT INTO h_gym_booking" +
					" (id_gym,id_user,id_slot,in_active,dt_active,id_associate,nm_name,nm_location,in_gender) VALUES" +
					" ("+ idCenter + "," + idUser + ",'"+ idSlot + "','Y',CURDATE(),'"+idAssociate+"','"+nmName+"','"+nmLocation+"','"+inGender+"')";
			if(bookSeatRegister(query1)){
				System.out.println("Gym: Successfully booked!!!");
				bean = new MessageBean("Success","Gym: Successfully booked!!!");
			}
		} else{
			System.out.println("No sheats available...");
			bean = new MessageBean("Warnning","No sheats available...");
		}
		return bean;
	}
	private boolean isSeatAvailable(Integer nrTotalSeatCapacity, Integer nrBookedSeat){
		System.out.println("Total seat: " +nrTotalSeatCapacity+ " , Booked seat: " +nrBookedSeat);
		
		boolean isAvailable = false;
		if(nrTotalSeatCapacity > nrBookedSeat){
			isAvailable = true;
		}
		return isAvailable;
	}
	public Integer getTotalBookedSeat(Integer nrTotalSeatCapacity,String query) {
		// TODO Auto-generated method stub
		System.out.println(query);
		Integer nrBookedSeat = 0;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			if (rset.next()) {
				nrBookedSeat = rset.getInt("BOOKED_SEAT");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return nrBookedSeat;
	}

	public MessageBean doYogaRegister(Integer idUser, Integer idCenter, Integer nrTotalSeatCapacity,String idSlot,
									  String idAssociate, String nmName, String nmLocation, String inGender) {
		// TODO Auto-generated method stub
		MessageBean bean = null;
		String query = "SELECT COUNT(*) AS booked_seat FROM h_yoga_booking h WHERE in_active='Y' and id_yoga=" + idCenter;
		Integer nrBookedSeat = getTotalBookedSeat(nrTotalSeatCapacity,query);
		System.out.println("Total / Booked - sheats: " + nrTotalSeatCapacity + " / " +nrBookedSeat);
		if(isSeatAvailable(nrTotalSeatCapacity,nrBookedSeat)){
			String query1 = "INSERT INTO h_yoga_booking" +
					" (id_yoga,id_user,id_slot,in_active,dt_active,id_associate,nm_name,nm_location,in_gender)" +
					" VALUES ("+ idCenter + "," + idUser + ",'"+ idSlot +"','Y',CURDATE(),'"+idAssociate+"','"+nmName+"','"+nmLocation+"','"+inGender+"')";
			if(bookSeatRegister(query1)){
				System.out.println("Yoga: Successfully booked!!!");
				bean = new MessageBean("Success","Yoga: Successfully booked!!!");
			}
		} else{
			System.out.println("No sheats available...");
			bean = new MessageBean("Warnning","No sheats available...");
		}
		return bean;
	}

	private boolean bookSeatRegister(String query) {
		// TODO Auto-generated method stub
		System.out.println(query);
		boolean returnValue = false;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			int row = stmt.executeUpdate(query);
			if (row != 0) {
				returnValue = true;
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue =  false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue = false;
		} finally {
			try {
				dao.closeConnection(stmt, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return returnValue;
	}

	public String getEmailId(Integer id_, Integer id_type) {
		// TODO Auto-generated method stub
		String query = null;
		if(id_type == 1){
			query = "SELECT id_email FROM m_gym WHERE id_gym=" + id_;
		} else if(id_type == 2){
			query = "SELECT id_email FROM m_yoga WHERE id_yoga=" + id_;
		}
		
		System.out.println(query);
		BaseDAO dao = new BaseDAO();
		String id_email = null;
		try {
			con = dao.openConnection();
			stmt = con.createStatement();			
			rset = stmt.executeQuery(query);
			if (rset.next()) {
				id_email = rset.getString("id_email");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return id_email;
	}

	public boolean saveGymYogaDetails(Integer idType,String strName, String idBranch, String idEmail, String nrTotalSeat, String nmFromSlotTime1, String nmToSlotTime1, String nmFromSlotTime2, String nmToSlotTime2, String nrFee){
		String query = null;
		if(idType==1){
			query = "INSERT INTO m_gym (nm_gym, id_branch, id_email, nr_total_seat, nr_fee, nm_slot_time1, nm_slot_time2) VALUES "
				+ "('" + strName + "','" + Integer.parseInt(idBranch) + "','"+idEmail+"','"+Integer.parseInt(nrTotalSeat)+"','"+Integer.parseInt(nrFee)+"','From "+nmFromSlotTime1+" To "+nmToSlotTime1+"','From "+nmFromSlotTime2+" To "+nmToSlotTime2+"')";
		} else{
			query = "INSERT INTO m_yoga (nm_yoga, id_branch, id_email, nr_total_seat, nr_fee, nm_slot_time1, nm_slot_time2) VALUES "
				+ "('" + strName + "','" + Integer.parseInt(idBranch) + "','"+idEmail+"','"+Integer.parseInt(nrTotalSeat)+"','"+Integer.parseInt(nrFee)+"','From "+nmFromSlotTime1+" To "+nmToSlotTime1+"','From "+nmFromSlotTime2+" To "+nmToSlotTime2+"')";
		}

		System.out.println(query);
		boolean returnValue = false;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			int row = stmt.executeUpdate(query);
			if (row != 0) {
				returnValue = true;
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue = false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue = false;
		} finally {
			try {
				dao.closeConnection(stmt, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return returnValue;
	}
}
